import numpy as np
import pandas as pd
import pymysql

class MysqlUtils(object):
    def __init__(self):
        self.conn = pymysql.connect(
            host='localhost',
            port=3306,
            user='root',
            password='root',
            db='scenic',
            charset='utf8',
        )
        
    def get_data(self):
        cursor=self.conn.cursor(cursor=pymysql.cursors.DictCursor)
        sql = """
        select 
        t.tourist_agency_name, rel.id_no, left(rel.id_no, 2) as province_code, DAYOFWEEK(gate.create_time)as weekend, 
        cast(substring(rel.id_no, 7, 4) as unsigned) as birth_year 
        from ticket_order_user_rel rel 
        join ticket_order t on t.id = rel.order_id
        join order_user_gate_rel gate on gate.ticket_rel_id = rel.id 
        where t.tourist_agency_name != '' and t.pay_time != '' 
        """
        
        cursor.execute(sql)
        data = cursor.fetchall()
        df = pd.DataFrame(data)
        df['non_weekend'] = df['weekend'].apply(lambda x: 1 if x  not in [1, 7] else 0)
        df['valid_id'] = df['id_no'].apply(lambda x: 1 if str(x).split() != '' else 0)
        df['eldery_ratio'] = df.apply(
            lambda x:1 if (x['valid_id'] and 2025 - x['birth_year'] >= 60) else 0 if x['valid_id'] else np.nan,
            axis=1
        )
        df['out_province'] = df.apply(
            lambda x:1 if (x['valid_id'] and x['province_code'] != '44') else 0 if x['valid_id'] else np.nan,
            axis=1
        )
        
        result=df.groupby('tourist_agency_name').agg(
            total_visitors=('id_no','count'),
            valid_visitors=('valid_id','sum'),
            non_weekend_ratio=('non_weekend','sum'),
            eldery_ratio=('eldery_ratio','sum'),
            out_province_ratio=('out_province','sum'),
        ).reset_index()
        result['eldery_ratio'] = result['eldery_ratio'] / result['valid_visitors'].replace(0, np.nan)
        result['out_province_ratio'] = result['out_province_ratio'] / result['valid_visitors'].replace(0, np.nan)
        result['non_weekend_ratio'] = result['non_weekend_ratio'] / result['total_visitors'].replace(0, np.nan)
        print(result)
        result.to_csv('scenic_data.csv', index=False)
        
        
if __name__ == '__main__':
    mu = MysqlUtils()
    mu.get_data()